package com.huan.dao;

import com.huan.entity.News;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class NewsDaoImpl extends BaseDao implements NewsDao {

    private Connection conn;
    //    private Statement stmt;
    private PreparedStatement pstmt; // 预防sql注入
    private ResultSet rs;


    /**
     * 查询数据库 - news_detail
     *
     * @return
     */
    public List<News> getNewsByCondition(int categoryID, String criteria)
    {

        String sql = "select * from news_detail where 1=1";
        List<Object> params = new ArrayList<>();
        if (categoryID > 0) {
            sql = sql + " and categoryId=?";
            params.add(categoryID);
        }
        if (criteria != null && !"".equals(criteria)) {
            sql = sql + " and title like ?";
            params.add("%" + criteria + "%");
        }
        return getNews(sql, params);
    }


    /**
     * 分页查询
     *
     * @param categoryID 类别 ID
     * @param criteria   标准
     * @param startIndex 开始索引，起始下标
     * @param pageSize   页面大小
     * @return
     */
    @Override
    public List<News> getNewListWithPaingInfo(int categoryID, String criteria, int startIndex, int pageSize)
    {
        String sql = "select * from news_detail where 1=1";
        List<Object> params = new ArrayList<>();
        if (categoryID > 0) {
            sql = sql + " and categoryId=?";
            params.add(categoryID);
        }
        if (criteria != null && !"".equals(criteria)) {
            sql = sql + " and title like ?";
            params.add("%" + criteria + "%");
        }
        sql = sql + " limit ?,?";
        params.add(startIndex);
        params.add(pageSize);
        return getNews(sql, params);
    }


    /**
     * 根据id获取数据
     *
     * @param id 新闻id
     * @return
     */
    @Override
    public News getNewsListById(int id)
    {
        String sql = "select * from news_detail where id=?";
        ResultSet rs = executeSQL(sql, new Object[]{id});
        try {
            while (rs != null && rs.next()) {
                String News_title = rs.getString("title");
                String summary = rs.getString("summary");
                String content = rs.getString("content");
                String author = rs.getString("author");
                String picpath = rs.getString("picpath");
                int categoryID = rs.getInt("categoryId");

                Timestamp time = rs.getTimestamp("createdate");
                News news = new News();
                news.setTitle(News_title);
                news.setSummary(summary);
                news.setContent(content);
                news.setAuthor(author);
                news.setPicpath(picpath);
                news.setCategoryId(categoryID);
                news.setCreatedate(time);
                return news;
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            closeResource();
        }
        return null;
    }

    /**
     * 根据id更新新闻
     *
     * @param news
     * @return
     */
    @Override
    public int updateNews(News news)
    {
        News originalNews = getNewsListById(news.getId());
        List<Object> params = new ArrayList<>();
        String sql = "update news_detail ";

        sql = sql + " set  modifydate= ? ,";
        params.add(new Timestamp(Calendar.getInstance().getTimeInMillis()));

        if (news.getCategoryId() != originalNews.getId()) {
            sql = sql + " categoryId= ? ,";
            params.add(news.getCategoryId());
        }
        if (news.getTitle() != null && !news.getTitle().equals(originalNews.getTitle())) {
            sql = sql + "  title= ? ,";
            params.add(news.getTitle());
        }
        if (news.getSummary() != null && !news.getSummary().equals(originalNews.getSummary())) {
            sql = sql + "  summary= ? ,";
            params.add(news.getSummary());
        }
        if (news.getPicpath() != null && !news.getPicpath().equals(originalNews.getPicpath())) {
            sql = sql + "  picpath= ? ,";
            params.add(news.getPicpath());
        }
        if (news.getContent() != null && !news.getContent().equals(originalNews.getContent())) {
            sql = sql + "  content= ? ,";
            params.add(news.getContent());
        }
        if (news.getAuthor() != null && !news.getAuthor().equals(originalNews.getAuthor())) {
            sql = sql + "  author= ? ,";
            params.add(news.getAuthor());
        }

        sql = sql.substring(0, sql.lastIndexOf(",") - 1) + " where id = ?";
        System.out.println(sql);
        params.add(news.getId());
        return executeUpdate(sql, params.toArray());
    }


    /**
     * 增加新闻
     *
     * @param news
     * @return
     */
    @Override
    public int addNews(News news)
    {
        String sql = "insert into news_detail (categoryId,title,summary,picpath,content,author,createdate) values (?,?,?,?,?,?,?)";
        return executeUpdate(sql, new Object[]{
                news.getCategoryId(),
                news.getTitle() == null ? "" : news.getTitle(),
                news.getSummary() == null ? "" : news.getSummary(),
                news.getPicpath() == null ? "" : news.getPicpath(),
                news.getContent() == null ? "" : news.getContent(),
                news.getAuthor() == null ? "" : news.getAuthor(),
                new Timestamp(Calendar.getInstance().getTimeInMillis())
        });
    }


    /**
     * 增加一条新闻
     *
     * @param categoryID： 1-6
     * @param title
     * @param summary
     * @param content
     * @param author
     * @return
     */
    public int addNews(int categoryID, String title, String summary, String content, String author)
    {
        String sql = "insert into news_detail (categoryId,title,summary,content,author,createdate ) values (?,?,?,?,?,?)";
        return executeUpdate(sql, new Object[]{categoryID, title, summary, content, author,
                new Timestamp(Calendar.getInstance().getTimeInMillis())});
    }

    /**
     * 根据ID删除一条新闻
     *
     * @param id
     * @return
     */
    public int deleteNews(int id)
    {
        String sql = "delete from news_detail where id=?";
        return executeUpdate(sql, new Object[]{id});
    }

    /**
     * 获取查询结果
     *
     * @param sql    SQL语句
     * @param params 参数
     * @return
     */
    private List<News> getNews(String sql, List<Object> params)
    {
        rs = executeSQL(sql, params.toArray());

        List<News> list = new ArrayList<>();

        // 4.获取查询结果
        try {
            while (rs != null && rs.next()) {
                int id = rs.getInt("id");
                int categoryId = rs.getInt("categoryId");
                String News_title = rs.getString("title");
                String summary = rs.getString("summary");
                String content = rs.getString("content");
                String author = rs.getString("author");
                Timestamp time = rs.getTimestamp("createdate");

                News news = new News();
                news.setId(id);
                news.setCategoryId(categoryId);
                news.setTitle(News_title);
                news.setSummary(summary);
                news.setContent(content);
                news.setAuthor(author);
                news.setCreatedate(time);
                list.add(news);
                System.out.println(news);
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            closeResource();
        }
        return list;
    }


    public static void main(String[] args)
    {
        //        new NewsDao().getNewsList( "%尼日%" );
        //        new NewsDao().updateNews( "2022年", 1 );
        // select * from news_detail where title like '%清零%' or 1=1 or title like '%111%'
        // select * from news_user where 1=1
        // select * from news_detail where ---; drop table news_detail
        int i = new NewsDaoImpl().addNews(1, "4月11日培华学院全体核酸检测", "4月11日培华学院全体核酸检测", "4月11日培华学院全体核酸检测",
                                          "计科20级学生");
        if (i > 0) {
            System.out.println("添加成功");
            // new NewsDaoImpl().getNewsList("%培华%");
        } else {
            System.out.println("添加失败");
        }

    }
}
